package com.nettest.dao.system;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import com.nettest.bean.BaseCriteriaVo;
import com.nettest.bean.system.StaffCriteriaVo;
import com.nettest.entity.system.Staff;
import com.nettest.service.BaseServiceForHibernate;
import com.nettest.util.AddScalar;
import com.nettest.util.StringUtils;
@Service
public class StaffDao extends BaseServiceForHibernate<Staff>{

	public List<Staff> queryStaffByCriteria(BaseCriteriaVo criteriaVo) {
		StaffCriteriaVo vo = (StaffCriteriaVo)criteriaVo;
		String sql = "select id,staffNo,staffName,deptName,sex,employDate," +
				"	activeStr from ( select row_.*, rownum rownum_ from (" + "select t.id," +
				"       t.staff_no staffNo," +
				"       t.staff_name staffName," +
				"       d.dept_name deptName," +
				"       t.sex sex," +
				"       t.employ_date employDate," +
				"       t.active activeStr" +
				"  from t_staff t" +
				"  left join t_department d on t.dept_id = d.dept_id" +
				"  where 1=1" ;
		if(!StringUtils.isBlank(vo.getStaffNo())) {
			sql += "   and t.staff_no like :staffNo";
		}
		if(!StringUtils.isBlank(vo.getStaffName())) {
			sql += "   and t.staff_name like :staffName";
		}
		if(!StringUtils.isBlank(vo.getDeptId())) {
			sql += "   and d.dept_id = :deptId";
		}
		
		if(!StringUtils.isBlank(vo.getActive())) {
			sql += "   and t.active = :active";
		}
		
		
		if(!StringUtils.isBlank(criteriaVo.getSortField())) {
			sql += "   order by " + criteriaVo.getSortField();
		}
		
		if(!StringUtils.isBlank(criteriaVo.getDescOrAsc())) {
			sql += " " + criteriaVo.getDescOrAsc() +  
					" ) row_ where rownum <= :maxResult) where rownum_ > :firstResult";
		} else {
			sql += " ) row_ where rownum <= :maxResult) where rownum_ > :firstResult";
		}
		
		SQLQuery query = (SQLQuery) getSession().createSQLQuery(sql);
		if(!StringUtils.isBlank(vo.getStaffNo())) {
			query.setString("staffNo", "%"+vo.getStaffNo()+"%");
		}
		if(!StringUtils.isBlank(vo.getStaffName())) {
			query.setString("staffName", "%"+vo.getStaffName()+"%");
		}
		if(!StringUtils.isBlank(vo.getDeptId())) {
			query.setString("deptId", vo.getDeptId());
		}
		
		if(!StringUtils.isBlank(vo.getActive())) {
			query.setString("active", vo.getActive());
		}
		
		query.setLong("firstResult", vo.getFirstResult());
		query.setLong("maxResult", vo.getMaxResults());

		List<String> fieldList = new ArrayList<String>();
		fieldList.add("id");
		fieldList.add("staffNo");
		fieldList.add("sex");
		fieldList.add("employDate");
		fieldList.add("staffName");
		fieldList.add("deptName");
		fieldList.add("activeStr");
		
		AddScalar.addSclar(query, Staff.class, fieldList);
		query.setResultTransformer(Transformers.aliasToBean(Staff.class));
		
		@SuppressWarnings("unchecked")
		List<Staff> list = query.list();
		return list;
	}
	public int totalSize(BaseCriteriaVo criteriaVo) {
		StaffCriteriaVo vo = (StaffCriteriaVo)criteriaVo;
		String sql = "select count(*) " +
				"  from t_staff t" +
				"  left join t_department d on t.dept_id = d.dept_id" +
				"  where 1=1" ;
		if(!StringUtils.isBlank(vo.getStaffNo())) {
			sql += "   and t.staff_no like :staffNo";
		}
		if(!StringUtils.isBlank(vo.getStaffName())) {
			sql += "   and t.staff_name like :staffName";
		}
		if(!StringUtils.isBlank(vo.getDeptId())) {
			sql += "   and d.dept_id = :deptId";
		}
		
		if(!StringUtils.isBlank(vo.getActive())) {
			sql += "   and t.active = :active";
		}
		
		SQLQuery query = (SQLQuery) getSession().createSQLQuery(sql);
		if(!StringUtils.isBlank(vo.getStaffNo())) {
			query.setString("staffNo", "%"+vo.getStaffNo()+"%");
		}
		if(!StringUtils.isBlank(vo.getStaffName())) {
			query.setString("staffName", "%"+vo.getStaffName()+"%");
		}
		if(!StringUtils.isBlank(vo.getDeptId())) {
			query.setString("deptId", vo.getDeptId());
		}
		
		if(!StringUtils.isBlank(vo.getActive())) {
			query.setString("active", vo.getActive());
		}
		Object uniqueResult = query.uniqueResult();
		if(uniqueResult != null) {
			return ((BigDecimal)uniqueResult).intValue();
		}
		return 0;
	}
}
